import datetime
import pymysql
import pandas as pd
import warnings

warnings.filterwarnings("ignore")


def read_sql(sql, db_name):
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db=db_name, **db_config)
    return pd.read_sql(sql, conn)


def fix_fill_times(binance_df, fill_times, expand_kline_list, step):
    """
    binance缺失数据之前的一条数据有可能会出现vol=0,o=h=l=c， 这样的数据也是无效数据需要替换
    :param binance_df:
    :param fill_times:
    :param expand_kline_list:
    :param step:
    :return:
    """
    for i, fill_time in enumerate(fill_times):
        before_missing_time = fill_time[0] - datetime.timedelta(seconds=step)
        if binance_df[binance_df["datetime"] == before_missing_time].iloc[0]["vol"] == 0:
            print(f"{before_missing_time}存在无效数据:{binance_df[binance_df["datetime"] == before_missing_time].iloc[0].to_dict()}")
            fill_times[i].insert(0, before_missing_time)
            expand_kline_list.append(before_missing_time)
    return fill_times


def fill_binance_missing_with_else_datasource(binance_df, else_df, frequency, datasource):
    assert frequency != "1d", "暂不支持填充缺失日线"
    cond1 = else_df["datetime"] <= binance_df["datetime"].max()
    cond2 = else_df["datetime"] >= binance_df["datetime"].min()
    else_df = else_df[cond1 & cond2]
    miss_times = list(set(else_df["datetime"]) - set(binance_df["datetime"]))
    miss_times.sort()
    #  把连续的缺失分为1组
    fill_times = [[miss_times[0]]]
    expand_kline_list = []  # binance存在但是无效的数据
    index = 0
    if frequency == "1h":
        step = 3600
    elif frequency == "5m":
        step = 300
    else:
        raise Exception("invalid frequency")
    for i in range(1, len(miss_times)):
        if (miss_times[i] - miss_times[i - 1]).total_seconds() == step:
            fill_times[index].append(miss_times[i])
        else:
            expand_one_step = fill_times[index][-1] + datetime.timedelta(seconds=step)
            fill_times[index].append(expand_one_step)
            expand_kline_list.append(expand_one_step)
            index += 1
            fill_times.append([])
            fill_times[index].append(miss_times[i])
    expand_one_step = fill_times[index][-1] + datetime.timedelta(seconds=step)
    expand_kline_list.append(expand_one_step)
    fill_times[index].append(expand_one_step)
    fix_fill_times(binance_df, fill_times, expand_kline_list, step)
    tmp_else_df_list = []
    print(f"总共从{datasource}填充{len(fill_times)}段数据")
    count = 0
    for fill_time_list in fill_times:
        count += 1
        # print(f"第{count}段,填充开始时间:{fill_time_list[0]},填充结束时间:{fill_time_list[-1]}")
        tmp_else_df = else_df[else_df["datetime"].isin(fill_time_list)]
        tmp_else_df_list.append(tmp_else_df)
    fill_else_df = pd.concat(tmp_else_df_list, ignore_index=True)
    binance_df = binance_df[~binance_df["datetime"].isin(expand_kline_list)]
    binance_df = pd.concat([binance_df, fill_else_df], ignore_index=True)
    binance_df.sort_values(by=["datetime"], inplace=True)
    binance_df.reset_index(drop=True, inplace=True)
    print(f"相比{datasource}缺失的数据长度:{len(miss_times)}, 填补的数据长度:{len(fill_else_df)}")
    if datasource == "bitstamp" or datasource == "glassnode":  # bitstamp或glassnode的数据都是完备的 可以用来辅助检查一下binance的数据
        else_df.sort_values(by=["datetime"], inplace=True)
        assert binance_df["datetime"].reset_index(drop=True).equals(else_df["datetime"].reset_index(drop=True))
    return binance_df


def merge_two_datasource_kline(frequency="1h"):
    df = read_sql(f"select * from k_line where symbol='BTC' and frequency='{frequency}'",
                  db_name="all_history_ohlcvm_coinmarketcap")
    binance_df = df[df["datasource"] == "binance"]
    binance_begin_time = datetime.datetime(2018, 1, 1)
    binance_df = binance_df.loc[binance_df['datetime'] >= binance_begin_time]
    okx_df = df[(df["datasource"] == "okx") & (df["type"] == "spot")]
    if frequency == "5m":
        choose_datasoure = 'bitstamp'  # 5m的k线2018年以前的数据用bitstamp的数据
    else:
        choose_datasoure = "glassnode"  # 1h或1d的k线2018年以前的数据用glassnode的数据
    choose_df = df[df["datasource"] == f"{choose_datasoure}"]
    if frequency in ["1h", "5m"]:  # 日线数据不存在缺失
        print(f"频率:{frequency},优先用okx填充binance的缺失,填充前binance的数据长度:{len(binance_df)}")
        binance_df = fill_binance_missing_with_else_datasource(binance_df, okx_df, frequency, "okx")
        print(f"频率:{frequency},填充okx后,binance数据长度:", len(binance_df))
        print(f"频率:{frequency},然后用{choose_datasoure}填充binance的缺失")
        binance_df = fill_binance_missing_with_else_datasource(binance_df, choose_df, frequency,
                                                               f"{choose_datasoure}")
        print(f"频率:{frequency}，填充{choose_datasoure}后,binance数据长度:", len(binance_df))
    choose_begin_time = datetime.datetime(2016, 1, 1)  # glassnode或bitstamp的开始时间
    choose_end_time = datetime.datetime(2018, 1, 1)
    choose_df = choose_df[(choose_df['datetime'] >= choose_begin_time) &
                          (choose_df['datetime'] < choose_end_time)]
    columns = ["datetime", "open", "high", "low", "close"]
    merge_df = pd.concat([binance_df[columns], choose_df[columns]])
    merge_df.sort_values(by=["datetime"], inplace=True)
    return merge_df


if __name__ == '__main__':
    BTC_kline_1d = merge_two_datasource_kline(frequency="1d")
    BTC_kline_1d.to_csv("BTC_kline_1d_merge.csv", index=False)
    BTC_kline_1d = merge_two_datasource_kline(frequency="1h")
    BTC_kline_1d.to_csv("BTC_kline_1h_merge.csv", index=False)
    BTC_kline_5m = merge_two_datasource_kline(frequency="5m")
    BTC_kline_5m.to_csv("BTC_kline_5m_merge.csv", index=False)
